package day1202friut;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import day1130.JDBCUtil;

public class FruitDao {
	
	//添加
	public static void insert(FruitModel1 f) {
		Connection conn = null;JDBCUtil.getConnection();
		PreparedStatement ps =null;
		String sql = "insert into fruit(name,saleprice,costprice) values(?,?,?)";
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setString(1, f.getName());
			ps.setDouble(2, f.getSaleprice());
			ps.setDouble(3, f.getCostprice());
			ps.execute();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
	}
	
	//查找水果，按名字查找
	public static ArrayList<FruitModel1> select(String name){
		ArrayList<FruitModel1> list = new ArrayList<FruitModel1>();
		Connection conn = null;
		PreparedStatement ps = null;
		ResultSet result = null;
		StringBuffer sql = new StringBuffer("select * from fruit");
		
		try {
			conn = JDBCUtil.getConnection();
			if(name!=null&&!name.trim().isEmpty()) {
				sql.append(" where name = ?");
				ps = conn.prepareStatement(sql.toString());
			    ps.setString(1, name);
			}else {
				sql.append(" order by soldnumber desc");
				ps = conn.prepareStatement(sql.toString());
			}
			result = ps.executeQuery();
			while(result.next()) {
				FruitModel1 fruit = new FruitModel1();
				fruit.setCostprice(result.getDouble("costprice"));
				fruit.setInprice(result.getDouble("inprice"));
				fruit.setInventory(result.getInt("inventory"));
				fruit.setName(result.getString("name"));
				fruit.setSaleprice(result.getDouble("saleprice"));
				fruit.setSoldnumber(result.getInt("soldnumber"));
				fruit.setTotalcount(result.getInt("totalcount"));
				fruit.setTotalprice(result.getDouble("totalprice"));
				list.add(fruit);
			}
			return list;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
			if(result!=null) {
				try {
					result.close();
				} catch (SQLException e) {
					// TODO Auto-generated catch block
					e.printStackTrace();
				}
				result = null;
			}
		}
		return list;
	}
	
	//更新数据
	public static int update(FruitModel1 f) {
		String sql = "update fruit set inprice = ?,inventory = ?,totalcount = ?,totalprice = ?,soldnumber = ? where name = ?";
		Connection conn = null;
		PreparedStatement ps = null;
		int res = 0;
		try {
			conn = JDBCUtil.getConnection();
			ps = conn.prepareStatement(sql);
			ps.setDouble(1, f.getInprice());
			ps.setInt(2, f.getInventory());
			ps.setInt(3, f.getTotalcount());
			ps.setDouble(4, f.getTotalprice());
			ps.setInt(5, f.getSoldnumber());
			ps.setString(6, f.getName());
			res = ps.executeUpdate();
			return res;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			JDBCUtil.close(conn, ps);
		}
		return res;
		
	}

}
